<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  
  <meta name="generator" content="Hugo 0.98.0" />

  
  <meta name="description" content="走在通往幸福的路上">
  

  
  <link rel="apple-touch-icon" sizes="180x180" href="https://blog.v5u.win/apple-touch-icon.png">

  
  <link rel="icon" type="image/png" sizes="32x32" href="https://blog.v5u.win/favicon-32x32.png">

  
  <link rel="icon" type="image/png" sizes="16x16" href="https://blog.v5u.win/favicon-16x16.png">

  
  <link rel="manifest" href="https://blog.v5u.win/site.webmanifest">

  
  <link rel="mask-icon" href="https://blog.v5u.win/safari-pinned-tab.svg" color="">

  <meta name="msapplication-TileColor" content="">

  <meta name="theme-color" content="">

  
  <link rel="stylesheet" href="https://blog.v5u.win/css/bootstrap.min.css" />

  
  <title>MySQL | 为吾优</title>
  

  <style>
body {
  min-width: 300px;
}

.custom-navbar {
  margin-bottom: 1em;
  height: 60px;
}

.custom-navbar a {
  display: inline-block; 
  padding: 18px 0;
  margin-right: 1em; 
  font-weight: bold; 
}

.custom-navbar a:hover,
.custom-navbar a:focus {
  text-decoration: none; 
}

@media print {
  .custom-navbar {
    display: none;
  }
}

article {
  padding-bottom: 1em;
}

img {
  max-width: 100%;
}


body {
  background-color: #fff;
}



body {
  color: #212529;
}



a {
  color: #007bff;
}



a:hover,
a:focus {
  color: #0056b3;
}



.custom-navbar {
  background-color: #212529;
}



.custom-navbar a {
  color: rgba(255,255,255,.75);
}



.custom-navbar a:hover,
.custom-navbar a:focus {
  color: rgba(255,255,255,1);
}



.container {
  max-width: 800px;
}





</style>
</head>

<body>
  <nav class="custom-navbar">
  <div class="container">
    
    <a href="/">文章</a>
    
    <a href="/tags/">标签</a>
    
    <a href="/about/">关于</a>
    
    <a href="/index.xml">RSS</a>
    
  </div>
</nav>
  
  <div class="container">
    <article>
      <h1>MySQL</h1>
<h3 id="mysql-经常使用tips">MYSQL 经常使用tips</h3>
<ul>
<li>terminal登录：路径 -u 用户名 -p //mac下 /usr/local/mysql/bin/mysql -u root -p</li>
<li>更改初始密码：set password for &lsquo;root&rsquo;@&rsquo;localhost&rsquo; = password(&rsquo;newPassword&rsquo;);</li>
</ul>
<h4 id="db-操作">db 操作</h4>
<pre tabindex="0"><code>* show databases;
* create NEW_DB;
* drop NEW_DB;
* use DBname;
</code></pre><h4 id="table-操作">table 操作</h4>
<pre tabindex="0"><code>* CREATE TABLE table_name (column_name column_type);
* DROP TABLE table_name ;
* INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
* SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
* DELETE FROM table_name [WHERE Clause]
</code></pre><h4 id="brew安装mysql后的提示">brew安装MySQL后的提示</h4>
<pre tabindex="0"><code>==&gt; ./mysql-test-run.pl status --vardir=/tmp/d20171222-28458-qrce7m
mysql的安装地址在这里
==&gt; /usr/local/homebrew/Cellar/mysql/5.7.20/bin/mysqld --initialize-insecure --user=fanjinlong --basedir=/usr/
==&gt; Caveats
我们已经为你安装了 MySQL数据库，有一个没有密码的root账户，如果要设置的话运行：
We&#39;ve installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation
MySQL配置的默认只允许从本地连接，连接的换运行命令：mysql -uroot
MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot
启动mysql 重新restart：
To have launchd start mysql now and restart at login:
  brew services start mysql
  或者如果你不想、不需要一个后台服务你可以只运行：
Or, if you don&#39;t want/need a background service you can just run:
  mysql.server start
==&gt; Summary
</code></pre><p>创建my.cnf配置文件</p>
<p>1.首先需要知道系统是按如下顺序去找my.cnf：</p>
<p>i.    /etc/my.cnf
ii.   /etc/mysql/my.cnf
iii.  /usr/local/etc/my.cnf
iv.  ~/.my.cnf</p>
<p>2.所以就在/etc下创建my.cnf</p>
<p>$ cd /etc</p>
<p>$ sudo vim my.cnf</p>
<p>配置文件内容如下：</p>
<p><a href="https://www.mmcaijing.com/2076.html">www.mmcaijing.com/2076.html</a></p>
<h4 id="navicat-无法连接通过brew-安装的-mysql">Navicat 无法连接通过brew 安装的 mysql</h4>
<p>提醒一句：如果直接 mysql 是 镜像市场直接下载的 记得配置：</p>
<p>​    MYSQL_ROOT_PASSWORD：你的密码</p>
<p>错误：</p>
<p>Client does not support authentication protocol requested by server; consider upgrading MySQL client</p>
<p>解决方案</p>
<ol>
<li>先登录：</li>
</ol>
<pre tabindex="0"><code>终端执行
mysql -u root -p
#接着输入你的密码
</code></pre><ol start="2">
<li>解决：</li>
</ol>
<pre tabindex="0"><code>ALTER USER &#39;root&#39;@&#39;%&#39; IDENTIFIED WITH mysql_native_password BY &#39;你的密码&#39;;

ALTER USER &#39;root&#39;@&#39;localhost&#39; IDENTIFIED WITH mysql_native_password BY &#39;你的密码&#39;;

SELECT plugin FROM mysql.user WHERE User = &#39;root&#39;;
</code></pre>
    </article>
  </div>

  
  
  

  
</body>

</html>